﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Diagnostics;

namespace EagleJournal
{
    class Class2
    {
        public List<String>[] viewallclient()
        {
            List<string>[] list = new List<string>[3];
            list[0] = new List<string>();
            list[1] = new List<string>();
            list[2] = new List<string>();
            try
            {
                MySqlConnection myConnection;
                string server;
                string database;
                string uid;
                string password;
                //MySqlDataReader dataReader;
                string query = "Select * from client;";
                server = "localhost";
                database = "travel_cmp_db";
                uid = "root";
                password = "p@ssword";
                string connectionString;
                connectionString = "SERVER=" + server + ";" + "DATABASE=" +
                database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

                myConnection = new MySqlConnection(connectionString);
                myConnection.Open();
                MySqlCommand cmd = new MySqlCommand(query, myConnection);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    Debug.WriteLine(dataReader["clientname"] + "");
                    list[0].Add(dataReader["clientid"] + "");
                    list[1].Add(dataReader["clientname"] + "");
                    list[2].Add(dataReader["contactInfo"] + "");
                }
                dataReader.Close();
                myConnection.Close();


            }
            catch (Exception e)
            {
                Debug.WriteLine(e.ToString());
            }
            return list;
        }
        public List<String>[] viewallPO()
        {
            List<string>[] list = new List<string>[7];
            list[0] = new List<string>();
            list[1] = new List<string>();
            list[2] = new List<string>();
            list[3] = new List<string>();
            list[4] = new List<string>();
            list[5] = new List<string>();
            list[6] = new List<string>();
            list[7] = new List<string>();
            try
            {
                MySqlConnection myConnection;
                string server;
                string database;
                string uid;
                string password;
                //MySqlDataReader dataReader;
                string query = "Select * from purchaseOrder;";
                server = "localhost";
                database = "travel_cmp_db";
                uid = "root";
                password = "p@ssword";
                string connectionString;
                connectionString = "SERVER=" + server + ";" + "DATABASE=" +
                database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

                myConnection = new MySqlConnection(connectionString);
                myConnection.Open();
                MySqlCommand cmd = new MySqlCommand(query, myConnection);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    //Debug.WriteLine(dataReader["clientname"] + "");
                    list[0].Add(dataReader["purchaseOrderid"] + "");
                    list[1].Add(dataReader["purchaseOrderSerial"] + "");
                    list[2].Add(dataReader["clientName"] + "");
                    list[3].Add(dataReader["dateIssued"] + "");
                    list[4].Add(dataReader["amount"] + "");
                    list[5].Add(dataReader["company"] + "");
                    list[6].Add(dataReader["description"] + "");
                    list[7].Add(dataReader["clientId"] + "");
                }
                dataReader.Close();
                myConnection.Close();


            }
            catch (Exception e)
            {
                Debug.WriteLine(e.ToString());
            }
            return list;
        }
        public List<String>[] viewalldeadline()
        {
            List<string>[] list = new List<string>[4];
            list[0] = new List<string>();
            list[1] = new List<string>();
            list[2] = new List<string>();
            list[3] = new List<string>();
            try
            {
                MySqlConnection myConnection;
                string server;
                string database;
                string uid;
                string password;
                //MySqlDataReader dataReader;
                string query = "Select * from deadline;";
                server = "localhost";
                database = "travel_cmp_db";
                uid = "root";
                password = "p@ssword";
                string connectionString;
                connectionString = "SERVER=" + server + ";" + "DATABASE=" +
                database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

                myConnection = new MySqlConnection(connectionString);
                myConnection.Open();
                MySqlCommand cmd = new MySqlCommand(query, myConnection);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    //Debug.WriteLine(dataReader["clientname"] + "");
                    list[0].Add(dataReader["clientid"] + "");
                    list[1].Add(dataReader["purchaseOrderid"] + "");
                    list[2].Add(dataReader["deadline"] + "");
                    list[3].Add(dataReader["status"] + "");

                }
                dataReader.Close();
                myConnection.Close();


            }
            catch (Exception e)
            {
                Debug.WriteLine(e.ToString());
            }
            return list;
        }
        public List<String>[] searchClientname(String input)
        {
            List<string>[] list = new List<string>[3];
            list[0] = new List<string>();
            list[1] = new List<string>();
            list[2] = new List<string>();
            try
            {
                MySqlConnection myConnection;
                string server;
                string database;
                string uid;
                string password;
                //MySqlDataReader dataReader;
                string query = "Select * from client where clientname like " + "%" + input + "%" + ";";
                server = "localhost";
                database = "travel_cmp_db";
                uid = "root";
                password = "p@ssword";
                string connectionString;
                connectionString = "SERVER=" + server + ";" + "DATABASE=" +
                database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

                myConnection = new MySqlConnection(connectionString);
                myConnection.Open();
                MySqlCommand cmd = new MySqlCommand(query, myConnection);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    //Debug.WriteLine(dataReader["clientname"] + "");
                    list[0].Add(dataReader["clientid"] + "");
                    list[1].Add(dataReader["clientname"] + "");
                    list[2].Add(dataReader["contactinfo"] + "");

                }
                dataReader.Close();
                myConnection.Close();


            }
            catch (Exception e)
            {
                Debug.WriteLine(e.ToString());
            }
            return list;
        }
        public List<String>[] searchClientContact(String input)
        {
            List<string>[] list = new List<string>[3];
            list[0] = new List<string>();
            list[1] = new List<string>();
            list[2] = new List<string>();
            try
            {
                MySqlConnection myConnection;
                string server;
                string database;
                string uid;
                string password;
                //MySqlDataReader dataReader;
                string query = "Select * from client where contactinfo like " + "%" + input + "%" + ";";
                server = "localhost";
                database = "travel_cmp_db";
                uid = "root";
                password = "p@ssword";
                string connectionString;
                connectionString = "SERVER=" + server + ";" + "DATABASE=" +
                database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

                myConnection = new MySqlConnection(connectionString);
                myConnection.Open();
                MySqlCommand cmd = new MySqlCommand(query, myConnection);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    //Debug.WriteLine(dataReader["clientname"] + "");
                    list[0].Add(dataReader["clientid"] + "");
                    list[1].Add(dataReader["clientname"] + "");
                    list[2].Add(dataReader["contactinfo"] + "");

                }
                dataReader.Close();
                myConnection.Close();


            }
            catch (Exception e)
            {
                Debug.WriteLine(e.ToString());
            }
            return list;
        }

    }

}
